﻿package com.agileai.miscdp.hotweb.database;

import java.util.HashMap;

import org.apache.commons.lang.StringUtils;

import com.agileai.miscdp.hotweb.domain.Column;
import com.agileai.miscdp.hotweb.domain.ResFileValueProvider;
import com.agileai.util.StringUtil;

/**
 * SQL构造器
 */
public class SqlBuilder {
	public static final String NEWLINE = "\r\n";
	private HashMap<String,HashMap<String,Column>> columns8tableMap = new HashMap<String,HashMap<String,Column>>();
	
	public void addColumnsMap(String tableName,HashMap<String,Column> columnMap){
		columns8tableMap.put(tableName,columnMap);
	}
	private String getParamValue(HashMap<String,Column> columnMap,String field){
		String result = "";
		if (columnMap != null && columnMap.get(field) != null){
			Column column = columnMap.get(field);
			if (!StringUtil.isNullOrEmpty(column.getJdbcTypeName())){
				result = field + ":" + column.getJdbcTypeName();
			}else{
				result = field;
			}
		}else{
			result = field;
		}
		return result;
	}
	
	public String findAllSql(String tableName,String[] colNames){
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		for (int i=0;i < colNames.length;i++){
			sql.append(colNames[i]);
			if (i < colNames.length-1){
				sql.append(",");
			}
		}
		sql.append(NEWLINE);
		sql.append("from ").append(tableName);
		return sql.toString();
	}
	public String findRecordsByForeignKeySql(String tableName,String[] colNames,String foreignKey,String masterKey,String sortField){
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		for (int i=0;i < colNames.length;i++){
			sql.append(colNames[i]);
			if (i < colNames.length-1){
				sql.append(",");
			}
		}
		sql.append(NEWLINE).append("from ").append(tableName).append(NEWLINE);
		sql.append("where ").append(foreignKey).append("=").append("#").append(masterKey).append("#");
		if (!StringUtil.isNullOrEmpty(sortField)){
			sql.append(NEWLINE).append("order by ").append(sortField);	
		}
		return sql.toString();
	}
	
	public String findRecordsByForeignKeySql(String tableName,String[] colNames,String foreignKey){
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		for (int i=0;i < colNames.length;i++){
			sql.append(colNames[i]);
			if (i < colNames.length-1){
				sql.append(",");
			}
		}
		sql.append(NEWLINE).append("from ").append(tableName).append(NEWLINE);
		sql.append("where 1=1").append(NEWLINE);
		sql.append("and ").append(foreignKey).append("=").append("#columnId#");		
		return sql.toString();
	}
	
	public String findRecordsMany2ManySql(String colTableName,String contentTableName,String relTableName
			,String[] contentColumns,String colTablePK,String contentTablePK){
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		for (int i=0;i < contentColumns.length;i++){
			sql.append("a."+contentColumns[i]);
			sql.append(",");
		}
		sql.append("b."+colTablePK);
		sql.append(NEWLINE);
		sql.append("from ").append(contentTableName).append(" a ,").append(colTableName).append(" b,");
		sql.append(relTableName).append(" c ").append(NEWLINE);
		sql.append("where a.").append(contentTablePK).append(" = c.").append(contentTablePK).append(NEWLINE);
		sql.append("and b.").append(colTablePK).append(" = c.").append(colTablePK).append(NEWLINE);
		sql.append("and b.").append(colTablePK).append("=#columnId#");
		return sql.toString();
	}
	
	public String queryCurLevelRecordsSql(String treeTableName,String[] colNames
			,String idField,String parentIdField,String sortField){
        StringBuffer tempQueryCurLevelSql = new StringBuffer();
        tempQueryCurLevelSql.append("select ");
        for (int i=0;i < colNames.length;i++){
			tempQueryCurLevelSql.append("b."+colNames[i]);
			if (i < colNames.length -1){
				tempQueryCurLevelSql.append(",");
			}
		}
		tempQueryCurLevelSql.append(NEWLINE);
		tempQueryCurLevelSql.append("from ").append(treeTableName).append(" a left join ").append(treeTableName).append(" b ").append(NEWLINE);
		tempQueryCurLevelSql.append("on a.").append(parentIdField).append("=").append("b.").append(parentIdField).append(NEWLINE);
		tempQueryCurLevelSql.append("where a.").append(idField).append("=").append("#value#").append(NEWLINE);
		tempQueryCurLevelSql.append("order by b.").append(sortField);
        String queryCurLevelRecordsSql = tempQueryCurLevelSql.toString();
        return queryCurLevelRecordsSql;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	private HashMap getPKMap(String[] primaryKeys){
		HashMap result = new HashMap();
		for (int i=0;i < primaryKeys.length;i++){
			result.put(primaryKeys[i],primaryKeys[i]);
		}
		return result;
	}
	public String retrieveSql(String[] colNames,String tableName,String[] primaryKeys){
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		if (colNames == null){
			sql.append(" * ");
		}
		else{
			for (int i=0;i < colNames.length;i++){
				sql.append(colNames[i]);
				if (i < colNames.length -1){
					sql.append(",");
				}
			}
		}
		sql.append(NEWLINE);
		sql.append("from ").append(tableName);
		sql.append(NEWLINE);
		sql.append("where 1=1 ");
		
		for (int i=0;i < primaryKeys.length;i++){
			sql.append(NEWLINE);
			sql.append("and ").append(primaryKeys[i]).append("=").append("#").append(primaryKeys[i]).append("#");
		}
		return sql.toString();
	}
	
	public String retrieveSqlByParams(String[] colNames,String tableName,String[] someFields){
		StringBuffer sql = new StringBuffer();
		sql.append("select ");
		if (colNames == null){
			sql.append(" * ");
		}
		else{
			for (int i=0;i < colNames.length;i++){
				sql.append(colNames[i]);
				if (i < colNames.length -1){
					sql.append(",");
				}
			}
		}
		sql.append(NEWLINE);
		sql.append("from ").append(tableName).append(NEWLINE);
		sql.append("where 1=1 ");
		for (int i=0;i < someFields.length;i++){
			String someField = someFields[i];
			sql.append(NEWLINE);
			sql.append("<isNotEmpty prepend=\"and \" property=\"" + someField+"\">").append(NEWLINE);
			sql.append(someField).append("=").append("#").append(someField).append("#").append(NEWLINE);
			sql.append("</isNotEmpty>");
		}
		return sql.toString();
	}
	
	public String queryMaxSortIdSql(String tableName,String sortField,String someField){
		String queryMaxSortIdSql = "select max("+sortField+") as MAX_"+sortField+" from "+ tableName;
		if (someField != null){
			queryMaxSortIdSql = queryMaxSortIdSql + " where "+someField+" = #value#";
		}
		return queryMaxSortIdSql;
	}
	
	public String insertSql(String tableName,String[] colNames){
		HashMap<String,Column> columnMap = this.columns8tableMap.get(tableName);
		StringBuffer sql = new StringBuffer();
		sql.append("insert into ").append(tableName);
		sql.append(" (");
		for (int i=0;i < colNames.length;i++){
			sql.append(colNames[i]);
			if (i < colNames.length-1){
				sql.append(",");
			}
		}
		sql.append(") values (");
		for (int i=0;i < colNames.length;i++){
			sql.append("#").append(this.getParamValue(columnMap, colNames[i])).append("#");
			if (i < colNames.length-1){
				sql.append(",");
			}
		}
		sql.append(")");
		return sql.toString();
	}
	public String insertSql(String tableName,String[] colNames,String preFix){
        String insertTreeRecordSql = null;
        HashMap<String,Column> columnMap = this.columns8tableMap.get(tableName);
		StringBuffer tempInsertSql = new StringBuffer();
		tempInsertSql.append("insert into ").append(tableName);
		tempInsertSql.append(" (");
		for (int i=0;i < colNames.length;i++){
			tempInsertSql.append(colNames[i]);
			if (i < colNames.length-1){
				tempInsertSql.append(",");
			}
		}
		tempInsertSql.append(") values (");
		for (int i=0;i < colNames.length;i++){
			tempInsertSql.append("#").append(preFix+this.getParamValue(columnMap,colNames[i])).append("#");
			if (i < colNames.length-1){
				tempInsertSql.append(",");
			}
		}
		tempInsertSql.append(")");
		insertTreeRecordSql = tempInsertSql.toString();
		return insertTreeRecordSql;
	}
	public String getResFileRecordsSQL(String tableName,ResFileValueProvider resFileValueProvider){
		StringBuffer sql = new StringBuffer();
		String pkName = resFileValueProvider.getPrimaryKeyField();
		String bizIdField = resFileValueProvider.getBizIdField();
		String resIdField = resFileValueProvider.getResIdField();
		sql.append("select a.").append(pkName).append(",a.").append(bizIdField).append(",a.").append(resIdField).append(",b.RES_NAME");
		sql.append(NEWLINE);
		sql.append("from ").append(tableName).append(" a JOIN wcm_general_resource b");
		sql.append(NEWLINE);
		sql.append("on a.").append(resIdField).append(" = b.RES_ID ");
		sql.append("where 1=1 and a.").append(bizIdField).append("=#").append(bizIdField).append("# order by b.RES_NAME");
		return sql.toString();
    }
	public String deleteSql(String tableName,String[] primaryKeys){
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ").append(tableName);
		for (int i=0;i < primaryKeys.length;i++){
			if (i == 0){
				sql.append(" where ").append(primaryKeys[i]).append("=")
					.append("#").append(primaryKeys[i]).append("#");
			}
			else if (i < primaryKeys.length-1){
				sql.append(NEWLINE);
				sql.append("and ").append(primaryKeys[i]).append("=")
					.append("#").append(primaryKeys[i]).append("#");
			}
		}
		return sql.toString();
	}
	public String deleteResFileSql(String tableName,String[] someFields){
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ").append(tableName);
		sql.append(NEWLINE);
		sql.append("where 1=1 ");
		for (int i=0;i < someFields.length;i++){
			String someField = someFields[i];
			sql.append(NEWLINE);
			sql.append("<isNotEmpty prepend=\"and \" property=\"" + someField+"\">").append(NEWLINE);
			sql.append(someField).append("=").append("#").append(someField).append("#").append(NEWLINE);
			sql.append("</isNotEmpty>");
		}
		return sql.toString();
	}
	public String deleteRelationSQL(String relTableName,String[] primaryKeys){
		StringBuffer sql = new StringBuffer("delete from "+relTableName);
		for (int i=0;i < primaryKeys.length;i++){
			sql.append(NEWLINE);
			if (i==0){
				sql.append("where ").append(primaryKeys[i])
					.append("=").append("#").append(primaryKeys[i]).append("#");		
			}
			else{
				sql.append("and ").append(primaryKeys[i])
					.append("=").append("#").append(primaryKeys[i]).append("#");
			}
		}
		return sql.toString();
	}
	
	public String deleteSql(String tableName,String someField){
		 String sql = "delete from "+ tableName +" where " + someField +" = #value#";
		return sql.toString();
	}
	public String deleteByForeignKeySql(String tableName,String foreignKey,String masterKey){
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ").append(tableName).append(NEWLINE);
		sql.append("where ").append(foreignKey).append("=").append("#").append(masterKey).append("#");		
		return sql.toString();
	}
	
	@SuppressWarnings({"rawtypes" })
	public String updateSql(String tableName,String[] colNames,String[] primaryKeys){
		StringBuffer sql = new StringBuffer();
		HashMap<String,Column> columnMap = this.columns8tableMap.get(tableName);
		HashMap pkMap = this.getPKMap(primaryKeys);
		sql.append("update ").append(tableName).append(NEWLINE);
		boolean isFirst = true;
		for (int i=0;i < colNames.length;i++){
			if (pkMap.containsKey(colNames[i])){
				continue;
			}
			if (isFirst){
				isFirst = false;
				sql.append("set ").append(colNames[i]).append("=");
				sql.append("#").append(getParamValue(columnMap, colNames[i])).append("#");
				sql.append(",");
			}else{
				if (i < colNames.length-1){
					sql.append(colNames[i]).append("=").append("#").append(getParamValue(columnMap, colNames[i])).append("#");
					sql.append(",");
				}
				else{
					sql.append(colNames[i]).append("=").append("#").append(getParamValue(columnMap, colNames[i])).append("#");
				}
			}
		}
		sql.append(NEWLINE);
		for (int i=0;i < primaryKeys.length;i++){
			if (i==0){
				sql.append("where ").append(primaryKeys[i])
					.append("=").append("#").append(primaryKeys[i]).append("#");
			}
			else{
				sql.append("and ").append(primaryKeys[i])
					.append("=").append("#").append(primaryKeys[i]).append("#");
			}
		}
		return sql.toString();
	}
	public String updateRelationSql(String tableName,String updateField,String otherField){
		HashMap<String,Column> columnMap = this.columns8tableMap.get(tableName);
		String sql = "update "+tableName+" set "+updateField+" = #NEW_"+getParamValue(columnMap, updateField)+"# " +
				" where "+updateField+" =#"+updateField+"# and "+otherField+" = #"+otherField+"#";
		return sql;
	}
	public String[] parseFields(String sql){
		String[] result = null;
		if (!StringUtils.isBlank(sql)){
			String tempSql = sql.toUpperCase();
			int selectPosition = tempSql.indexOf("SELECT");
			int fromPosition = tempSql.indexOf("FROM");
			String subedSql = tempSql.substring(selectPosition+6,fromPosition);
			String[] tempArray = StringUtils.splitByWholeSeparator(subedSql,",");
			result = processFields(tempArray);
		}
		return result;
	}
	private String[] processFields(String[] tempArray){
		int length = tempArray.length;
		String[] result = new String[length];
		for (int i=0;i < length;i++){
			result[i] = processField(tempArray[i]);
		}
		return result;
	}
	private String processField(String field){
		String result = "";
		result = field.trim();
		int pointPostion = result.indexOf(".");
		if (pointPostion > -1){
			result = result.substring(pointPostion+1,result.length());
		}
		int asPostion = result.indexOf(" AS "); 
		if (asPostion > -1){
			result = result.substring(asPostion+2,result.length());
			result = result.trim();
		}
		return result;
	}
	public String parseFindDetailSql(String findListSql,String[] rsIds){
		StringBuffer sql = new StringBuffer("select * from (");
		String tempSql = findListSql.toUpperCase();
		int lastWherePosition = tempSql.lastIndexOf("WHERE");
		String subedSql = findListSql.substring(0,lastWherePosition);
		sql.append(subedSql).append(") aaa where 1=1 "); 
		for (int j=0;j < rsIds.length;j++){
			String rsId = rsIds[j];
			sql.append(" and ");
			sql.append(rsId).append("=").append("#").append(rsId).append("#");
		}
		return sql.toString();
	}
}
